package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import javax.xml.crypto.Data;

import modelo.RelServicos;

public class DaoRelServicos {

    public ArrayList<RelServicos> consultarRelServicos(Date dataini, Date datafin) {
    	
    	ArrayList<RelServicos> servicos = new ArrayList<RelServicos>();
    	
      try {
        Class.forName("org.postgresql.Driver");
            Connection con = null;
             con = DriverManager.getConnection("jdbc:postgresql:Siafu","postgres", "postgresql123");
       if (con != null)
          {
                
         
    	   		  Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

                  String query="select nome_serv, count(nome_serv), sum(valor_serv) from servico, beneficiario, venda, plano, tipo_servico, item " +
                  		       "where servico.dt_servico >= '" + dataini + "' and " + 
                  		       "servico.dt_servico <= '" + datafin + "' and " +
                  		       "servico.id_benef = beneficiario.id_benef and " +
                		       "beneficiario.id_venda = venda.id_venda and " +
                		  	   "venda.id_plano = plano.id_plano and " +
                		  	   "item.id_plano = plano.id_plano and " +
                		  	   "item.id_tipo_serv = tipo_servico.id_tipo_serv " +
                		  	   "group by nome_serv;";
                  ResultSet rs = stmt.executeQuery(query);
                  
                  while(rs.next()){
                	  RelServicos rel = new RelServicos();
                	  
                	    int cols = rs.getMetaData().getColumnCount();
                	    Object[] arr = new Object[cols];
                	    for(int i=0; i<cols; i++){
                	      arr[i] = rs.getObject(i+1);
                	      rel.setNome_serv(rs.getString(1));                	      
                	      rel.setQtd_tipos_serv(rs.getInt(2));
                	      rel.setVal_servicos(rs.getFloat(3));
                	    }
                	    servicos.add(rel);
                	}
                  
          }
          else
              System.out.println("Connection Failed!");
      }
          catch(Exception e){
                              e.printStackTrace();
          }
      
    
      
      return servicos;
    
    }
    
    
}
